package com.ubst.smartad.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.ubst.smartad.dao.TSAAccountDO;
import com.ubst.smartad.dao.TSAUserAccountDO;
import com.ubst.smartad.logger.Debug;

public class TSAAccountMngImpl {
	
	Connection con = null;
	private String serverName;
	private Debug debug;
	
	/** 
     * 생성자
     *   
    */ 
	public TSAAccountMngImpl(Connection con, String serverName, String log_level) {
		this.con = con;
		this.serverName = serverName;
		this.debug = new Debug("TSAAccountMngImpl", log_level);
	}
	
	/**
	 * 관리대상 시스템의 계정정보 등록
	 * @param userAccountDO
	 * @throws SQLException
	 */
	public void insertAccount(TSAAccountDO accountDO) throws SQLException {
		PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        
        try {
            queryBuf = new StringBuffer();
			queryBuf.append("INSERT INTO TSA_ACCOUNT " )
				.append(" ( ")
				.append("  SYSTEMID ,ACCOUNTID ,PASSWD ,OLDPASSWD ,UID ")
				.append(" ,GID ,ACCGROUP ,ACCHOME ,ACCSHELL ,EXPIREDATE ")
				.append(" ,PASSCHANGE ,PASSMAXDAY ,PASSMINDAY ,PASSWARN ,INACTIVEDAY ")
				.append(" ,SYSTYPE ,ACCLOCK ,SCOMMENT ,LASTLOGIN ,LASTLOGINIP ")
				.append(" ,NSTATUS ,CRESULT, LASTSYNC ")
				.append(" ) ")
				.append(" VALUES")
				.append(" ( ") 
				.append("  ? ,? ,? ,? ,? ")
				.append(" ,? ,? ,? ,? ,? ")
				.append("  ? ,? ,? ,? ,? ")
				.append(" ,? ,? ,? ,? ,? ")
				.append(" ,? ,? ,? ")
				.append(" ) ");

			debug.debug("insertAccount ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, accountDO.systemid);
			pstmt.setString(idx++, accountDO.accountid);
			pstmt.setString(idx++, accountDO.passwd);
			pstmt.setString(idx++, accountDO.oldpasswd);
			pstmt.setString(idx++, accountDO.uid);
			pstmt.setString(idx++, accountDO.gid);
			pstmt.setString(idx++, accountDO.accgroup);
			pstmt.setString(idx++, accountDO.acchome);
			pstmt.setString(idx++, accountDO.accshell);
			pstmt.setString(idx++, accountDO.expiredate);
			pstmt.setString(idx++, accountDO.passchange);
			pstmt.setInt(idx++, accountDO.passmaxday);
			pstmt.setInt(idx++, accountDO.passminday);
			pstmt.setInt(idx++, accountDO.passwarn);
			pstmt.setInt(idx++, accountDO.inactiveday);
			pstmt.setString(idx++, accountDO.systype);
			pstmt.setString(idx++, accountDO.acclock);
			pstmt.setString(idx++, accountDO.scomment);
			pstmt.setString(idx++, accountDO.lastlogin);
			pstmt.setString(idx++, accountDO.lastloginip);
			pstmt.setInt(idx++, accountDO.nstatus);
			pstmt.setString(idx++, accountDO.cresult);
			pstmt.setString(idx++, accountDO.lastsync);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;
        } catch (SQLException e){
			throw e;
        } finally {
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }
	}
	
	/**
	 * 관리대상 시스템의 계정정보 수정
	 * @param userAccountDO
	 * @throws SQLException
	 */
	public void updateAccount(TSAAccountDO accountDO) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {
            queryBuf = new StringBuffer();
			queryBuf.append("UPDATE TSA_ACCOUNT " )
				.append(" SET ")
				.append("  SYSTEMID=? ,ACCOUNTID=? ,PASSWD=? ,OLDPASSWD=? ,UID=? ")
				.append(" ,GID=? ,ACCGROUP=? ,ACCHOME=? ,ACCSHELL=? ,EXPIREDATE=? ")
				.append(" ,PASSCHANGE=? ,PASSMAXDAY=? ,PASSMINDAY=? ,PASSWARN=? ,INACTIVEDAY=? ")
				.append(" ,SYSTYPE=? ,ACCLOCK=? ,SCOMMENT=? ,LASTLOGIN=? ,LASTLOGINIP=? ")
				.append(" ,NSTATUS=? ,CRESULT=?, LASTSYNC=? ")
				.append(" WHERE SYSTEMID=? AND ACCOUNTID=? ");

			debug.debug("updateAccount ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, accountDO.systemid);
			pstmt.setString(idx++, accountDO.accountid);
			pstmt.setString(idx++, accountDO.passwd);
			pstmt.setString(idx++, accountDO.oldpasswd);
			pstmt.setString(idx++, accountDO.uid);
			pstmt.setString(idx++, accountDO.gid);
			pstmt.setString(idx++, accountDO.accgroup);
			pstmt.setString(idx++, accountDO.acchome);
			pstmt.setString(idx++, accountDO.accshell);
			pstmt.setString(idx++, accountDO.expiredate);
			pstmt.setString(idx++, accountDO.passchange);
			pstmt.setInt(idx++, accountDO.passmaxday);
			pstmt.setInt(idx++, accountDO.passminday);
			pstmt.setInt(idx++, accountDO.passwarn);
			pstmt.setInt(idx++, accountDO.inactiveday);
			pstmt.setString(idx++, accountDO.systype);
			pstmt.setString(idx++, accountDO.acclock);
			pstmt.setString(idx++, accountDO.scomment);
			pstmt.setString(idx++, accountDO.lastlogin);
			pstmt.setString(idx++, accountDO.lastloginip);
			pstmt.setInt(idx++, accountDO.nstatus);
			pstmt.setString(idx++, accountDO.cresult);
			pstmt.setString(idx++, accountDO.lastsync);
			pstmt.setString(idx++, accountDO.systemid);
			pstmt.setString(idx++, accountDO.accountid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }
	
	/**
	 * 관리대상 시스템의 계정정보 목록 조회
	 * @param userid
	 * @return
	 * @throws SQLException
	 */
	public TSAAccountDO selectAccount(String systemid, String accountid) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		TSAAccountDO accountDO = null;

		try {
			
			StringBuffer queryBuf = new StringBuffer();
			queryBuf.append("SELECT ")
				.append("  SYSTEMID ,ACCOUNTID ,PASSWD ,OLDPASSWD ,UID ")
				.append(" ,GID ,ACCGROUP ,ACCHOME ,ACCSHELL ,EXPIREDATE ")
				.append(" ,PASSCHANGE ,PASSMAXDAY ,PASSMINDAY ,PASSWARN ,INACTIVEDAY ")
				.append(" ,SYSTYPE ,ACCLOCK ,SCOMMENT ,LASTLOGIN ,LASTLOGINIP ")
				.append(" ,NSTATUS ,CRESULT, LASTSYNC ")
				.append(" FROM TSA_ACCOUNT ")
				.append(" WHERE SYSTEMID=? AND ACCOUNTID=? ");

			debug.debug("selectAccountList ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, systemid);
			pstmt.setString(idx++, accountid);

			rs = pstmt.executeQuery();

			if ( rs.next() ){
				accountDO = new TSAAccountDO();
				accountDO.systemid = systemid;
				accountDO.accountid = accountid;
				accountDO.passwd = rs.getString("PASSWD");
				accountDO.oldpasswd = rs.getString("OLDPASSWD");
				accountDO.uid = rs.getString("UID");
				accountDO.gid = rs.getString("GID");
				accountDO.accgroup = rs.getString("ACCGROUP");
				accountDO.acchome = rs.getString("ACCHOME");
				accountDO.accshell = rs.getString("ACCSHELL");
				accountDO.expiredate = rs.getString("EXPIREDATE");
				accountDO.passchange = rs.getString("PASSCHANGE");
				accountDO.passmaxday = rs.getInt("PASSMAXDAY");
				accountDO.passminday = rs.getInt("PASSMINDAY");
				accountDO.passwarn = rs.getInt("PASSWARN");
				accountDO.inactiveday = rs.getInt("INACTIVEDAY");
				accountDO.systype = rs.getString("SYSTYPE");
				accountDO.acclock = rs.getString("ACCLOCK");
				accountDO.scomment = rs.getString("SCOMMENT");
				accountDO.lastlogin = rs.getString("LASTLOGIN");
				accountDO.lastloginip = rs.getString("LASTLOGINIP");
				accountDO.nstatus = rs.getInt("NSTATUS");
				accountDO.cresult = rs.getString("CRESULT");
				accountDO.lastsync = rs.getString("LASTSYNC");
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return accountDO;
	}
	
	/**
	 * 관리대상 시스템의 계정정보 삭제
	 * @param userid
	 * @throws SQLException
	 */
	public void deleteAccount(String systemid, String accountid) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

            queryBuf = new StringBuffer();
			queryBuf.append("DELETE FROM TSA_ACCOUNT " )
				.append(" WHERE SYSTEMID=? AND ACCOUNTID=? ");

			debug.debug("deleteAccount ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, systemid);
			pstmt.setString(idx++, accountid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }
}
